package com.phoenix.jsef.demo.test;

import com.phoenix.jsef.jdbcutils.CommandType;
import com.phoenix.jsef.jdbcutils.DBTablesCollection;
import com.phoenix.jsef.jdbcutils.SqlHelper;
import org.junit.Test;
import org.springframework.beans.factory.annotation.Autowired;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @version V1.0
 * @ClassName: JdbcHelperDemo
 * @Description:${todo}(这里用一句话描述这个类的作用)
 * @author: 李启联
 * @date: 2018-02-06 11:04
 * @Copyright: 2018 www.micoo.com Inc. All rights reserved.
 * 申明：本内容未经作者本人同意,禁止外泄以及用于其他的商业项目.
 */
public class JdbcHelperDemo extends TestBase {

    @Autowired
    private SqlHelper sqlHelper;

    @Test
    /**
     *  通过SQL查询语句或存储过程返回单个表的数据
     *  返回：List<Map<String,Object>>
     */
    public void getSingleTable() throws Exception {
        String sqlText ="SELECT * FROM OZ_BK_HEADER WHERE JOB_ORDER_ID='BK0G610OAP4N2NZ';";
        List resultTable = sqlHelper.getResultList(CommandType.Text,sqlText);
        System.out.println(resultTable);
    }

    @Test
    /**
     *  通过增强型SQL查询语句或存储过程返回单个表的数据，可支持防sql注入
     *  返回：List<Map<String,Object>>
     */
    public void getSingleTableSecurity() throws Exception {
        String jobOrderId ="BK0G610OAP4N2NZ" , rpId ="R";
        String sqlText ="SELECT * FROM SZ_FREIGHT WHERE JOB_ORDER_ID=? and RP_ID=?;";
        List param = new ArrayList();
        param.add(jobOrderId);
        param.add(rpId);

        List resultTable = sqlHelper.queryForList(CommandType.Text,sqlText,param.toArray());
        System.out.println(resultTable);
    }

    @Test
    public void getLocalTableSecurity() throws Exception {

        Map<String,Object> param = new HashMap<>();
        param.put("rp_id","R");
        param.put("JOB_ORDER_ID","BK0G610OAP4N2NZ");
        List resultTable = sqlHelper.queryForList(CommandType.LoadResourceFile,"getFreight",param);
        System.out.println(resultTable);
    }

    @Test
    public void getLocalTables() throws Exception {
        Map<String,Object> param = new HashMap<>();
        param.put("JOB_ORDER_ID","BK0G610OAP4N2NZ");
        Map<String,List> tables = sqlHelper.queryForDataSet(CommandType.LoadResourceFile,"getHeaderTables",param);
        System.out.println(tables);
    }

    @Test
    /**
     * 返回多张表的数据 Map形式返回
     */
    public void getTables() throws Exception{
        String sqlText ="SELECT * FROM OZ_BK_HEADER WHERE JOB_ORDER_ID='BK0G610OAP4N2NZ';\r\n" +
                        "SELECT * FROM SZ_FREIGHT WHERE JOB_ORDER_ID='BK0G610OAP4N2NZ';\r\n";
        Map<String, List> resultTables = sqlHelper.queryForDataSet(CommandType.Text,sqlText);
        System.out.println(resultTables);
    }

    @Test
    /**
     * 返回多张表的数据,以封装的DBTablesCollection 形式返回
     */
    public void getDBTablesCollection() throws Exception {
        String sqlText ="SELECT * FROM OZ_BK_HEADER WHERE JOB_ORDER_ID='BK0G610OAP4N2NZ';\r\n" +
                        "SELECT * FROM SZ_FREIGHT WHERE JOB_ORDER_ID='BK0G610OAP4N2NZ';\r\n";
        DBTablesCollection collection = sqlHelper.queryForTablesCollection(CommandType.Text,sqlText);
        collection.stream().forEach(System.out::print);
        //System.out.println(collection);
    }

    @Test
    /**
     * 新增数据
     */
    public void addRecord() throws Exception{
        String sqlText = "insert into T1(id,name) values ('00001','Teacher Cang');"+
                         "insert into T1(id,name) values ('00002','Teacher Jia');" ;
        int i = sqlHelper.excuteNonQuery(CommandType.Text,sqlText);
        System.out.println(i==-1 ? "新增失败" :"新增成功");
    }

    @Test
    /**
     * 增强型新增数据 防sql注入
     */
    public void addRecordSecurity() throws Exception{
        String sqlText = "insert into T1(id,name) values (?,?);"+
                         "insert into T1(id,name) values (?,?);";
        int i = sqlHelper.excuteNonQuery(CommandType.Text,sqlText,"00001","Teacher Cang","00002","Teacher Jia");
        System.out.println(i);
    }

    @Test
    /**
     * 修改数据
     */
    public void modifyRecord() throws Exception{
        String sqlText = "update T1 set name='Teacher Jia 00002' where id='00002';";
        int i = sqlHelper.excuteNonQuery(CommandType.Text,sqlText);
        System.out.println(i);
    }

    @Test
    /**
     * 增强型修改数据 防sql注入
     */
    public void modifySecurity() throws Exception{
        String sqlText = "update T1 set name='Teacher Jia 00002' where id=? ;";
        int i = sqlHelper.excuteNonQuery(CommandType.Text,sqlText,"00002");
        System.out.println(i);
    }

    @Test
    /**
     * 删除数据
     */
    public void deleteRecord() throws Exception{
        String sqlText = "delete from T1 where id='00001';";
        int i = sqlHelper.excuteNonQuery(CommandType.Text,sqlText);
        System.out.println(i);
    }

    @Test
    /**
     * 增强型删除数据 防sql注入
     */
    public void deleteSecurity() throws Exception{
        String sqlText = "delete from T1 where id=? ;";
        int i = sqlHelper.excuteNonQuery(CommandType.Text,sqlText,"00002");
        System.out.println(i);

    }

}
